set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prFgPickCartonExcelRpt_test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[prFgPickCartonExcelRpt_test]
GO
create proc prFgPickCartonExcelRpt_test
@compCode nchar(10),
@pickingNo nvarchar(20),
@iniColumn nchar(1),
@page int =1,
@pageSize int =100,
@sort nvarchar(20),
@order nvarchar(10)
WITH ENCRYPTION
as

set nocount on

declare @ttlRow int 

select d.style_type styleType, d.style_no styleNo, d.season_code season, e.season_desc seasonDesc,
		d.color_code colorCode, d.color_desc colorDesc, d.size, c.sku, 
		sum(c.qty) qty,  rtrim(fg_pick_no) pickingNo, 
		convert(dec(14, 2), isnull(g.mark_price, 0)) markPrice,
		convert(dec(14, 2), isnull(gg.mark_price, 0)) chnMarkPrice, d.style_type_desc styleTypeDesc,
		convert(dec(14,2), isnull(h.price, 0)) cost
into #temp_issue
from igsnet.dbo.fg_pick_hdr a
	 join igsnet.dbo.fg_trx_hdr b  on a.fg_pick_no = b.pick_no and b.trx_type = 'PC'
	 join igsnet.dbo.fg_trx_dtl c on b.trx_id = c.trx_id
	 join igsnet.dbo.v_pos_sku_info d on c.sku = d.sku
	 left outer join igsnet.dbo.ms_style_season e on d.season_code = e.season_code
	 join igsnet.dbo.pos_shop ee on a.shop_code = ee.shop_code
	 join igsnet.dbo.pos_region f on ee.region_code = f.region_code
	 left outer join  igsnet.dbo.ms_style_price g on d.style_no = g.style_no and 
				f.price_grp = g.price_grp and (c.sku = g.sku or g.sku = '')
	 left outer join  igsnet.dbo.ms_style_price gg on d.style_no = gg.style_no and 
				gg.price_grp ='CHN GROUP' and (c.sku = gg.sku or gg.sku = '')
	 left outer join dcpickingdetail h on b.pick_no = h.pickingNo and c.sku = h.sku
where d.brand in('ALPHA', 'Hype', 'RE-BK', 'SUPERUN', 'W.V.W', 'Other') and a.shop_code = 'GZCX'--a.fg_pick_no = @pickingNo 
group by d.style_type,  fg_pick_no , d.season_code, d.color_desc, 
		c.sku, d.style_no, d.color_code, d.size, e.season_desc, 
		convert(dec(14, 2),isnull(g.mark_price, 0)),
		convert(dec(14, 2), isnull(gg.mark_price, 0)),
		d.style_type_desc, isnull(h.price, 0)
 
select @ttlRow = @@rowcount

select styleType, styleNo ,        season,     seasonDesc ,          colorCode,      colorDesc ,
                                         size,            sku ,            sum(qty) as qty, markPrice, chnMarkPrice, styleTypeDesc
 from #temp_issue
 group by styleType, styleNo ,        season,     seasonDesc ,          colorCode,      colorDesc ,
                                         size,            sku ,        markPrice, chnMarkPrice, styleTypeDesc
										 order by styleNo, colorCode
set nocount off

go
exec prFgPickCartonExcelRpt_test N'KOYO',N'KO-IS0006255',N'N',N'1',N'65000',NULL,NULL


